import logging
import sqlite3
from datetime import datetime
from typing import Any, Dict, List, Literal, Optional, Sequence, Tuple, Type

import pandas as pd
from pydantic import TypeAdapter
from pydantic_core import PydanticUndefined
from PySide6.QtCore import (
    QAbstractTableModel,
    QModelIndex,
    QSortFilterProxyModel,
    Qt,
    Signal,
)
from PySide6.QtWidgets import (
    QComboBox,
    QDialog,
    QDialogButtonBox,
    QFormLayout,
    QHBoxLayout,
    QLineEdit,
    QMessageBox,
)

from src.models.base import IdenticalModel
from src.utils.utils import camel_to_snake

logger = logging.getLogger(__name__)


class SqlitePaginationModel(QAbstractTableModel):
    """用于处理SQLite数据分页和表格展示的模型，支持完整CRUD操作和筛选"""

    def __init__(
        self,
        db_path: str,
        model_class: Type[IdenticalModel],
        parent=None,
        display_non_data_fields_dict: Optional[Dict[str, str]] = None,
    ):
        super().__init__(parent)
        self.parent_widget = parent
        self.db_path = db_path
        try:
            self.conn = sqlite3.connect(self.db_path)
        except sqlite3.Error as e:
            logger.error(f"数据库连接失败: {e}")
            raise e
        self.sort_field = "id"
        self.sort_order = Qt.SortOrder.AscendingOrder
        self.model_class = model_class
        self.current_page = 1
        self.items_per_page = 50  # 默认每页50条
        self.total_items = 0
        self.current_data: List[IdenticalModel] = []

        # 筛选相关属性
        self.filter_text = ""
        self.filter_fields: List[str] = []

        # 从Pydantic模型获取字段信息
        self.display_data_fields: List[str] = []
        self.display_data_field_names: List[str] = []
        self.display_non_data_fields: List[str] = []
        self.display_non_data_field_names: List[str] = []
        self.hidden_data_fields: List[str] = []
        self.hidden_data_field_names: List[str] = []
        self.hidden_non_data_fields: List[str] = []
        self.hidden_non_data_field_names: List[str] = []

        last_field_entries = []  # 保存 (name, field_title) 对

        for name, field in self.model_class.model_fields.items():
            display_name = field.title or name  # 使用 title 作为显示名，没有则用字段名
            field_json_schema_extra = field.json_schema_extra or {}
            if field_json_schema_extra.get(
                "hidden", False
            ) and field_json_schema_extra.get("non_data_field", False):
                self.hidden_non_data_fields.append(name)
                self.hidden_non_data_field_names.append(display_name)
            elif field_json_schema_extra.get("hidden", False):
                self.hidden_data_fields.append(name)
                self.hidden_data_field_names.append(display_name)
            elif field_json_schema_extra.get("non_data_field", False):
                self.display_non_data_fields.append(name)
                self.display_non_data_field_names.append(display_name)
            else:
                # 普通字段，先加入列表
                if field_json_schema_extra.get("last_field", False):
                    last_field_entries.append((name, display_name))
                    continue
                self.display_data_fields.append(name)
                self.display_data_field_names.append(display_name)

        # 最后将特殊字段追加到列表末尾
        for name, display_name in last_field_entries:
            self.display_data_fields.append(name)
            self.display_data_field_names.append(display_name)

        # 如果需要操作列，添加到字段列表
        if display_non_data_fields_dict:
            self.display_non_data_fields.extend(display_non_data_fields_dict.keys())
            self.display_non_data_field_names.extend(
                display_non_data_fields_dict.values()
            )

        self.display_fields = self.display_data_fields + self.display_non_data_fields
        self.display_field_names = (
            self.display_data_field_names + self.display_non_data_field_names
        )
        self.data_fields = self.display_data_fields + self.hidden_data_fields
        self.data_field_names = (
            self.display_data_field_names + self.hidden_data_field_names
        )
        self.add_row_dialog, self.add_row_inputs = self._prepare_add_row_dialog()
        # 初始化时加载总数和第一页数据
        self._update_total_count()
        self.load_page(1)

    def __del__(self):
        if hasattr(self, "conn") and self.conn:
            self.conn.close()

    def _get_table_name(self) -> str:
        """从模型类获取表名"""
        return camel_to_snake(self.model_class.__name__)

    def _build_filter_conditions(self) -> Tuple[str, List[Any]]:
        """
        构建筛选条件的SQL片段和参数
        返回: (WHERE子句, 参数列表)
        """
        if not self.filter_text:
            return "", []

        # 确定要筛选的字段
        fields_to_filter = (
            self.filter_fields if self.filter_fields else self.data_fields
        )

        # 构建LIKE条件
        conditions = []
        params = []

        for field in fields_to_filter:
            # 检查字段是否存在于模型中
            if field not in self.model_class.model_fields:
                continue

            # 对字符串类型字段使用LIKE筛选
            field_type = self.model_class.model_fields[field].annotation
            if field_type is str or field_type is Optional[str]:
                conditions.append(f"{field} LIKE ?")
                params.append(f"%{self.filter_text}%")

        if not conditions:
            return "", []

        return f"WHERE {' OR '.join(conditions)}", params

    def _update_total_count(self) -> None:
        """更新总记录数（考虑筛选条件）"""
        table_name = self._get_table_name()
        try:
            cursor = self.conn.cursor()
            # 获取筛选条件
            filter_clause, params = self._build_filter_conditions()
            cursor.execute(f"SELECT COUNT(*) FROM {table_name} {filter_clause}", params)

            self.total_items = cursor.fetchone()[0]
            logger.info(f"获取到 {self.total_items} 条记录（考虑筛选条件）")
            cursor.close()
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "数据库错误", f"获取记录总数失败: {str(e)}"
            )
            logger.error(f"数据库错误，获取记录总数失败: {str(e)}")
            self.total_items = 0

    def rowCount(self, parent: QModelIndex = QModelIndex()) -> int:
        return len(self.current_data)

    def columnCount(self, parent: QModelIndex = QModelIndex()) -> int:
        return len(self.display_fields)

    def data(self, index: QModelIndex, role: int = Qt.ItemDataRole.DisplayRole) -> Any:
        if not index.isValid():
            return None

        row = index.row()
        col = index.column()

        if row >= len(self.current_data):
            return None

        item = self.current_data[row]
        field = self.display_fields[col]
        # value = getattr(item, field_name, None)

        if role == Qt.ItemDataRole.DisplayRole or role == Qt.ItemDataRole.EditRole:
            if field not in self.data_fields:
                return None
            value = item.model_dump(mode="json", include={field})[field]
            return str(value) if value is not None else ""
        elif role == Qt.ItemDataRole.UserRole:
            return item
        return None

    def headerData(
        self,
        section: int,
        orientation: Qt.Orientation,
        role: int = Qt.ItemDataRole.DisplayRole,
    ) -> Any:
        if (
            orientation == Qt.Orientation.Horizontal
            and role == Qt.ItemDataRole.DisplayRole
        ):
            if section < len(self.display_field_names):
                return self.display_field_names[section]
        elif (
            orientation == Qt.Orientation.Horizontal
            and role == Qt.ItemDataRole.UserRole
        ):
            return self.display_fields[section]
        return None

    def setData(
        self, index: QModelIndex, value: Any, role: int = Qt.ItemDataRole.EditRole
    ) -> bool:
        if index.isValid() and role == Qt.ItemDataRole.EditRole:
            row = index.row()
            col = index.column()

            if row >= len(self.current_data):
                return False

            item = self.current_data[row]
            field_name = self.display_fields[col]
            item_dict = item.model_dump(mode="python")
            try:
                # 尝试将值转换为正确的类型
                field_type = self.model_class.model_fields[field_name].annotation
                if field_type is not None:
                    type_adapter = TypeAdapter(field_type)
                    converted_value = type_adapter.validate_python(value)

                    if converted_value == item_dict[field_name]:
                        return True
                    item_dict[field_name] = converted_value
                    item = self.model_class(**item_dict)

                # 更新数据库
                self._update_database(item, field_name, converted_value)
                self.dataChanged.emit(index, index)

                # 如果修改的是筛选字段，可能需要重新筛选
                if not self.filter_fields or field_name in self.filter_fields:
                    self._update_total_count()
                    self.load_page(self.current_page)

                return True
            except Exception as e:
                logger.error(f"数据库错误，更新数据失败: {str(e)}")
                QMessageBox.warning(
                    self.parent_widget, "数据错误", f"无法更新数据: {str(e)}"
                )
                return False
        return False

    def _update_database(
        self, item: IdenticalModel, field_name: str, value: Any
    ) -> None:
        """更新数据库中的记录"""
        table_name = self._get_table_name()

        # 假设每个模型都有一个主键字段"id"
        if not hasattr(item, "id"):
            raise ValueError("模型必须包含'id'字段作为主键")
        json_schema_extra = self.model_class.model_fields[field_name].json_schema_extra
        if json_schema_extra and json_schema_extra.get("uneditable"):
            logger.warning(f"字段 {field_name} 被标记为'uneditable'，无法更新")
            return
        try:
            cursor = self.conn.cursor()
            # 处理None值和字符串转义
            if value is None:
                sql = f"UPDATE {table_name} SET {field_name} = NULL, last_modified_time = ? WHERE id = ?"
                cursor.execute(sql, (datetime.now(), item.id))
            else:
                sql = f"UPDATE {table_name} SET {field_name} = ?, last_modified_time = ? WHERE id = ?"
                cursor.execute(sql, (str(value), datetime.now(), item.id))
            self.conn.commit()
            cursor.close()
        except Exception as e:
            raise Exception(f"数据库更新失败: {str(e)}")

    def flags(self, index: QModelIndex) -> Qt.ItemFlag:
        if not index.isValid():
            return Qt.ItemFlag.NoItemFlags
        # 允许单元格被编辑
        field = self.display_fields[index.column()]
        if field in self.display_non_data_fields:
            return (
                super().flags(index)
                | Qt.ItemFlag.ItemIsEnabled
                | Qt.ItemFlag.ItemIsSelectable
            )
        json_schema_extra = self.model_class.model_fields[field].json_schema_extra
        if json_schema_extra and json_schema_extra.get("uneditable"):
            return (
                super().flags(index)
                | Qt.ItemFlag.ItemIsEnabled
                | Qt.ItemFlag.ItemIsSelectable
            )
        return (
            super().flags(index)
            | Qt.ItemFlag.ItemIsEditable
            | Qt.ItemFlag.ItemIsEnabled
            | Qt.ItemFlag.ItemIsSelectable
        )

    def load_page(self, page_num: int) -> bool:
        """加载指定页的数据（考虑筛选和排序）"""
        if page_num < 1:
            return False

        table_name = self._get_table_name()
        offset = (page_num - 1) * self.items_per_page
        sort_direction = (
            "ASC" if self.sort_order == Qt.SortOrder.AscendingOrder else "DESC"
        )
        sort_sql = f"ORDER BY {self.sort_field} {sort_direction}"

        # 获取筛选条件
        filter_clause, params = self._build_filter_conditions()
        # 添加分页参数
        params.extend([self.items_per_page, offset])

        try:
            self.beginResetModel()
            self.conn.row_factory = sqlite3.Row
            cursor = self.conn.cursor()

            # 执行带筛选、排序和分页的查询
            cursor.execute(
                f"SELECT {', '.join(self.data_fields)} FROM {table_name} {filter_clause} {sort_sql} LIMIT ? OFFSET ?",
                params,
            )

            self.current_data = []
            for row in cursor.fetchall():
                row_dict = dict(row)
                self.current_data.append(self.model_class(**row_dict))

            self.current_page = page_num
            cursor.close()
            self.endResetModel()
            return True
        except Exception as e:
            self.endResetModel()
            QMessageBox.critical(
                self.parent_widget, "数据库错误", f"加载数据失败: {str(e)}"
            )
            return False

    def sort(self, column: int, order: Qt.SortOrder) -> None:
        """重写QAbstractTableModel的sort方法，支持按列排序"""
        if column < 0 or column >= len(self.data_fields):
            return

        self.sort_field = self.data_fields[column]
        self.sort_order = order
        self.load_page(self.current_page)

    def set_items_per_page(self, count: int) -> None:
        """设置每页显示的记录数"""
        if count != self.items_per_page and count > 0:
            self.items_per_page = count
            self._update_total_count()
            self.load_page(1)  # 重置到第一页

    def total_pages(self) -> int:
        """计算总页数（考虑筛选条件）"""
        if self.total_items == 0:
            return 1
        return (self.total_items + self.items_per_page - 1) // self.items_per_page

    def add_one(self, item_data: Dict[str, Any]) -> bool:
        """添加单条记录"""
        try:
            # 验证数据
            item = self.model_class(**item_data)
            item_data = item.model_dump(include=set(self.data_fields))
            # 插入数据库
            table_name = self._get_table_name()
            fields = list(item_data.keys())
            placeholders = ", ".join(["?"] * len(fields))

            cursor = self.conn.cursor()
            cursor.execute(
                f"INSERT INTO {table_name} ({', '.join(fields)}) VALUES ({placeholders})",
                tuple(item_data.values()),
            )
            self.conn.commit()

            # 获取插入的ID（如果有自增ID）
            if "id" not in item_data and hasattr(item, "id"):
                setattr(item, "id", cursor.lastrowid)

            cursor.close()

            # 更新总数并重新加载当前页
            self._update_total_count()

            # 检查新添加的项是否符合筛选条件
            matches_filter = True
            if self.filter_text:
                matches_filter = False
                fields_to_check = (
                    self.filter_fields if self.filter_fields else self.data_fields
                )
                for field in fields_to_check:
                    value = str(getattr(item, field, "")).lower()
                    if self.filter_text in value:
                        matches_filter = True
                        break

            # 如果符合筛选条件且当前页是最后一页且未满，直接添加到当前数据
            if (
                matches_filter
                and len(self.current_data) < self.items_per_page
                and self.current_page == self.total_pages()
            ):
                self.beginInsertRows(
                    QModelIndex(), len(self.current_data), len(self.current_data)
                )
                self.current_data.append(item)
                self.endInsertRows()
            else:
                self.load_page(self.current_page)

            return True
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "添加失败", f"无法添加记录: {str(e)}"
            )
            return False

    def add_one_model(self, item: IdenticalModel) -> bool:
        """添加单条记录"""
        try:
            item_data = item.model_dump(include=set(self.data_fields))
            # 插入数据库
            table_name = self._get_table_name()
            fields = list(item_data.keys())
            placeholders = ", ".join(["?"] * len(fields))

            cursor = self.conn.cursor()
            cursor.execute(
                f"INSERT INTO {table_name} ({', '.join(fields)}) VALUES ({placeholders})",
                tuple(item_data.values()),
            )
            self.conn.commit()

            # 获取插入的ID（如果有自增ID）
            if "id" not in item_data and hasattr(item, "id"):
                setattr(item, "id", cursor.lastrowid)

            cursor.close()

            # 更新总数并重新加载当前页
            self._update_total_count()

            # 检查新添加的项是否符合筛选条件
            matches_filter = True
            if self.filter_text:
                matches_filter = False
                fields_to_check = (
                    self.filter_fields if self.filter_fields else self.data_fields
                )
                for field in fields_to_check:
                    value = str(getattr(item, field, "")).lower()
                    if self.filter_text in value:
                        matches_filter = True
                        break

            # 如果符合筛选条件且当前页是最后一页且未满，直接添加到当前数据
            if (
                matches_filter
                and len(self.current_data) < self.items_per_page
                and self.current_page == self.total_pages()
            ):
                self.beginInsertRows(
                    QModelIndex(), len(self.current_data), len(self.current_data)
                )
                self.current_data.append(item)
                self.endInsertRows()
            else:
                self.load_page(self.current_page)

            return True
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "添加失败", f"无法添加记录: {str(e)}"
            )
            return False

    def add_batch(self, items_data: List[Dict[str, Any]]) -> bool:
        """批量添加记录"""
        if not items_data:
            return True
        msgs = []
        failed_items = []
        valid_items = []
        for data in items_data:
            try:
                item = self.model_class(**data)
                valid_items.append(item)
            except Exception as e:
                logger.error(f"Failed to create model instance: {e}")
                failed_items.append(data)
                continue
        if not valid_items:
            QMessageBox.warning(self.parent_widget, "批量添加失败", "无有效记录")
            logger.error("批量添加记录时失败：无有效记录")
            return False
        if failed_items:
            msgs.append(f"验证失败记录数: {len(failed_items)}")
        try:
            # 转换为字典
            items_data = [
                item.model_dump(include=self.data_fields) for item in valid_items
            ]
            table_name = self._get_table_name()
            fields = list(items_data[0].keys())
            placeholders = ", ".join(["?"] * len(fields))
            values = [tuple(data.values()) for data in items_data]

            cursor = self.conn.cursor()
            cursor.execute("BEGIN TRANSACTION")
            try:
                cursor.executemany(
                    f"INSERT INTO {table_name} ({', '.join(fields)}) VALUES ({placeholders})",
                    values,
                )
                self.conn.commit()
            except Exception as e:
                self.conn.rollback()
                raise e
            finally:
                cursor.close()

            # 更新总数并重新加载当前页
            self._update_total_count()
            self.load_page(self.current_page)
            msgs.append(f"成功添加记录数: {len(valid_items)}")
            return True
        except Exception as e:
            msgs.append(f"批量添加记录时失败: {e}")
            logger.error(f"批量添加记录时失败: {e}")
            return False
        finally:
            QMessageBox.information(self.parent_widget, "批量添加信息", "\n".join(msgs))

    def delete(self, index: QModelIndex) -> bool:
        """删除指定索引的记录"""
        if not index.isValid():
            return False

        row = index.row()
        if row >= len(self.current_data):
            return False

        item = self.current_data[row]
        if not hasattr(item, "id"):
            QMessageBox.warning(
                self.parent_widget, "删除失败", "模型必须包含'id'字段作为主键"
            )
            return False

        try:
            # 从数据库删除
            table_name = self._get_table_name()
            cursor = self.conn.cursor()
            cursor.execute("BEGIN TRANSACTION")
            try:
                cursor.execute(f"DELETE FROM {table_name} WHERE id = ?", (item.id,))
                self.conn.commit()
                logger.info(f"Successfully deleted item with id {item.id}")
            except Exception as e:
                self.conn.rollback()
                logger.error(f"Failed to delete item with id {item.id}: {e}")
                raise e
            finally:
                cursor.close()

            # 从当前数据中删除
            self.beginRemoveRows(QModelIndex(), row, row)
            del self.current_data[row]
            self.endRemoveRows()

            # 更新总数
            self._update_total_count()

            # 如果当前页变空且不是第一页，加载上一页
            if len(self.current_data) == 0 and self.current_page > 1:
                self.load_page(self.current_page - 1)

            return True
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "删除失败", f"无法删除记录: {str(e)}"
            )
            return False

    def delete_batch(self, indices: List[QModelIndex]) -> bool:
        """批量删除指定索引的记录"""
        if not indices:
            return True

        # 获取唯一的ID列表
        ids = []
        rows_to_remove = []
        for idx in indices:
            if idx.isValid() and idx.row() not in rows_to_remove:
                rows_to_remove.append(idx.row())
                item = self.current_data[idx.row()]
                if hasattr(item, "id"):
                    ids.append(item.id)

        if not ids:
            return True

        try:
            # 从数据库删除
            table_name = self._get_table_name()
            cursor = self.conn.cursor()
            placeholders = ", ".join(["?"] * len(ids))
            cursor.execute("BEGIN TRANSACTION")
            try:
                cursor.execute(
                    f"DELETE FROM {table_name} WHERE id IN ({placeholders})", tuple(ids)
                )
                self.conn.commit()
                logger.info(f"Successfully deleted items with ids {ids}")
            except Exception as e:
                self.conn.rollback()
                logger.error(f"Failed to delete items with ids {ids}: {e}")
                raise e
            finally:
                cursor.close()

            # 从当前数据中删除（按降序删除，避免索引问题）
            for row in sorted(rows_to_remove, reverse=True):
                self.beginRemoveRows(QModelIndex(), row, row)
                del self.current_data[row]
                self.endRemoveRows()

            # 更新总数
            self._update_total_count()

            # 如果当前页变空且不是第一页，加载上一页
            if len(self.current_data) == 0 and self.current_page > 1:
                self.load_page(self.current_page - 1)

            return True
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "批量删除失败", f"无法批量删除记录: {str(e)}"
            )
            return False

    def delete_batch_by_models(self, models: List[IdenticalModel]) -> bool:
        """批量删除指定模型的记录"""
        if not models:
            return True
        # 获取唯一的ID列表
        ids = []
        for model in models:
            if hasattr(model, "id"):
                ids.append(model.id)
        if not ids:
            return True
        try:
            # 从数据库删除
            table_name = self._get_table_name()
            cursor = self.conn.cursor()
            placeholders = ", ".join(["?"] * len(ids))
            cursor.execute("BEGIN TRANSACTION")
            try:
                cursor.execute(
                    f"DELETE FROM {table_name} WHERE id IN ({placeholders})", tuple(ids)
                )
                self.conn.commit()
                logger.info(f"Successfully deleted items with ids {ids}")
            except Exception as e:
                self.conn.rollback()
                logger.error(f"Failed to delete items with ids {ids}: {e}")
                raise e
            finally:
                cursor.close()
            # 更新总数
            self._update_total_count()
            self.load_page(self.current_page)
            return True
        except Exception as e:
            QMessageBox.critical(
                self.parent_widget, "批量删除失败", f"无法批量删除记录: {str(e)}"
            )
            return False

    def get_item(self, index: QModelIndex) -> Optional[IdenticalModel]:
        """获取指定索引的项目"""
        if index.isValid() and index.row() < len(self.current_data):
            return self.current_data[index.row()]
        return None

    def set_filter(
        self, filter_text: str, filter_fields: Optional[List[str]] = None
    ) -> None:
        """设置筛选条件并重新加载数据"""
        self.filter_text = filter_text
        self.filter_fields = filter_fields if filter_fields is not None else []
        self.current_page = 1  # 筛选条件改变时重置到第一页
        self._update_total_count()
        self.load_page(1)

    def _prepare_add_row_dialog(self):
        # 弹出对话框让用户输入数据
        dialog = QDialog(self.parent_widget)
        dialog.setWindowTitle("添加新记录")
        form = QFormLayout(dialog)

        # 为每个字段创建输入框
        inputs = {}
        for field, field_name in zip(self.data_fields, self.data_field_names):
            field_info = self.model_class.model_fields[field]
            json_schema_extra = field_info.json_schema_extra
            if json_schema_extra and json_schema_extra.get("uneditable"):
                continue
            # 创建输入框
            default_value = getattr(field_info, "default", "")
            if default_value == PydanticUndefined or default_value is None:
                default_value = ""
            description = getattr(field_info, "description", None)
            # 如果字段是Literal类型，则使用combox
            if (
                field_info.annotation
                and getattr(field_info.annotation, "__origin__", None) is Literal
            ):
                inputs[field] = QComboBox()
                inputs[field].addItems(field_info.annotation.__args__)
            else:
                inputs[field] = QLineEdit(
                    text=str(default_value), placeholderText=description
                )

            # 获取字段描述信息

            # 创建布局来包含输入框和问号图标
            field_layout = QHBoxLayout()
            field_layout.addWidget(inputs[field])

            # # 如果有描述信息，添加问号图标和悬浮提示
            # if description:
            #     # 创建更美观的问号按钮
            #     help_button = QToolButton()
            #     help_button.setText("ℹ️")
            #     help_button.setToolTip(description)
            #     help_button.setStyleSheet("""
            #         QToolButton {
            #             border: none;
            #             background: transparent;
            #             color: #666666;
            #             font-size: 14px;
            #         }
            #         QToolButton:hover {
            #             color: #333333;
            #             background-color: #f0f0f0;
            #             border-radius: 8px;
            #         }
            #     """)
            #     help_button.setCursor(Qt.CursorShape.WhatsThisCursor)
            #     field_layout.addWidget(help_button)

            form.addRow(field_name, field_layout)

        # 添加确定/取消按钮
        buttons = QDialogButtonBox(
            QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel
        )
        buttons.accepted.connect(dialog.accept)
        buttons.rejected.connect(dialog.reject)
        form.addRow(buttons)
        return dialog, inputs

    def handle_add_row(self):
        new_row = {}
        if self.add_row_dialog.exec() == QDialog.DialogCode.Accepted:
            # 收集LineEdit和ComboBox的内容
            for field, widget in self.add_row_inputs.items():
                if isinstance(widget, QLineEdit):
                    new_row[field] = widget.text()
                elif isinstance(widget, QComboBox):
                    new_row[field] = widget.currentText()
            # 调用现有的add_one方法
            self.add_one(new_row)

    def update_or_insert_batch_dict(self, items_data: List[Dict[str, Any]]) -> bool:
        if not items_data:
            return True
        msgs = []
        failed_items = []
        valid_items = []
        for data in items_data:
            try:
                item = self.model_class(**data)
                valid_items.append(item)
            except Exception as e:
                logger.error(f"Failed to create model instance: {e}")
                failed_items.append(data)
                continue
        if not valid_items:
            QMessageBox.warning(self.parent_widget, "批量添加失败", "无有效记录")
            logger.error("批量添加记录时失败：无有效记录")
            return False
        if failed_items:
            msgs.append(f"验证失败记录数: {len(failed_items)}")

        try:
            items_data = [
                item.model_dump(include=self.data_fields) for item in valid_items
            ]
            table_name = self._get_table_name()
            fields = list(items_data[0].keys())
            placeholders = ", ".join(["?"] * len(fields))

            # 构建UPSERT语句
            update_fields = ", ".join(
                [f"{field}=excluded.{field}" for field in fields if field != "id"]
            )
            query = f"""
                INSERT INTO {table_name} ({", ".join(fields)}) 
                VALUES ({placeholders})
                ON CONFLICT(id) DO UPDATE SET {update_fields}
            """
            values = [tuple(item.values()) for item in items_data]
            cursor = self.conn.cursor()
            cursor.execute("BEGIN TRANSACTION")
            try:
                # 使用executemany批量执行
                cursor.executemany(query, values)
                self.conn.commit()
            except Exception as e:
                self.conn.rollback()
                raise e
            finally:
                cursor.close()
            # 更新总数并重新加载当前页
            self._update_total_count()
            self.load_page(self.current_page)
            msgs.append(f"成功添加记录数: {len(valid_items)}")
            return True
        except Exception as e:
            msgs.append(f"批量添加记录时失败: {e}")
            logger.error(f"批量添加记录时失败: {e}")
            return False
        finally:
            QMessageBox.information(self.parent_widget, "批量添加信息", "\n".join(msgs))

    def update_or_insert_batch_models(
        self, items_data: Sequence[IdenticalModel]
    ) -> bool:
        if not items_data:
            return True
        msgs = []
        try:
            items_data_dict = [
                item.model_dump(include=set(self.data_fields)) for item in items_data
            ]
            table_name = self._get_table_name()
            fields = list(items_data_dict[0].keys())
            placeholders = ", ".join(["?"] * len(fields))

            # 构建UPSERT语句
            update_fields = ", ".join(
                [f"{field}=excluded.{field}" for field in fields if field != "id"]
            )
            query = f"""
                INSERT INTO {table_name} ({", ".join(fields)}) 
                VALUES ({placeholders})
                ON CONFLICT(id) DO UPDATE SET {update_fields}
            """
            values = [tuple(item.values()) for item in items_data_dict]
            cursor = self.conn.cursor()
            cursor.execute("BEGIN TRANSACTION")
            try:
                # 使用executemany批量执行
                cursor.executemany(query, values)
                self.conn.commit()
            except Exception as e:
                self.conn.rollback()
                raise e
            finally:
                cursor.close()
            # 更新总数并重新加载当前页
            self._update_total_count()
            self.load_page(self.current_page)
            msgs.append(f"成功添加记录数: {len(items_data_dict)}")
            return True
        except Exception as e:
            msgs.append(f"批量添加记录时失败: {e}")
            logger.error(f"批量添加记录时失败: {e}")
            return False
        finally:
            QMessageBox.information(self.parent_widget, "批量添加信息", "\n".join(msgs))

    def get_all_data(self) -> List[IdenticalModel]:
        """获取所有数据"""
        table_name = self._get_table_name()
        fields = ",".join(self.data_fields)
        query = f"SELECT {fields} FROM {table_name}"
        cursor = self.conn.cursor()
        cursor.execute(query)
        rows = cursor.fetchall()
        cursor.close()
        return [self.model_class(**dict(zip(self.data_fields, row))) for row in rows]

    def export_to_excel(self, file_path: str) -> bool:
        """导出数据到Excel文件"""
        try:
            all_datas = self.get_all_data()
        except Exception as e:
            QMessageBox.warning(self.parent_widget, "导出数据", f"获取数据时出错: {e}")
            logger.error(f"获取数据时出错: {e}")
            return False

        if not all_datas:
            QMessageBox.warning(
                self.parent_widget, "导出数据", "当前数据库中无数据可导出"
            )
            return False
        try:
            df = pd.DataFrame([item.model_dump(by_alias=True) for item in all_datas])
            df.to_excel(file_path, index=False)
            QMessageBox.information(
                self.parent_widget, "导出数据", f"数据已成功导出到 {file_path}"
            )
            return True
        except Exception as e:
            QMessageBox.warning(
                self.parent_widget, "导出数据", f"导出数据到 {file_path} 时出错: {e}"
            )
            logger.error(f"导出数据到 {file_path} 时出错: {e}")
            return False


class PaginationSortFilterProxyModel(QSortFilterProxyModel):
    """筛选代理模型，用于对SqlitePaginationModel进行数据筛选"""

    on_sort = Signal()

    def __init__(self, parent=None):
        super().__init__(parent)
        self._filter_text = ""
        self._filter_fields = []

    def setSourceModel(self, model: SqlitePaginationModel, /) -> None:
        """
        设置源模型
        :param model: 要设置的源模型
        """
        super().setSourceModel(model)
        # 确保源模型是SqlitePaginationModel
        if not isinstance(model, SqlitePaginationModel):
            logger.error("FilterProxyModel 仅支持 SqlitePaginationModel 作为源模型")
            raise ValueError("FilterProxyModel 仅支持 SqlitePaginationModel 作为源模型")

    def sourceModel(self) -> SqlitePaginationModel:
        """
        获取当前设置的源模型
        :return: 当前设置的源模型
        """
        model = super().sourceModel()
        if isinstance(model, SqlitePaginationModel):
            return model  # type: ignore
        else:
            logger.error("FilterProxyModel 仅支持 SqlitePaginationModel 作为源模型")
            raise ValueError("FilterProxyModel 仅支持 SqlitePaginationModel 作为源模型")

    def setFilter(
        self, filter_text: str, filter_fields: Optional[List[str]] = None
    ) -> None:
        """
        设置筛选条件
        :param filter_text: 筛选文本
        :param filter_fields: 要筛选的字段列表，None表示所有字段
        """
        self._filter_text = filter_text.lower().strip()
        self._filter_fields = filter_fields if filter_fields is not None else []
        # 通知模型数据已更改，触发重新筛选
        self.invalidateFilter()

        # 如果源模型是SqlitePaginationModel，通知其更新筛选条件
        source_model = self.sourceModel()
        if isinstance(source_model, SqlitePaginationModel):
            source_model.set_filter(self._filter_text, self._filter_fields)

    def filterAcceptsRow(self, source_row: int, source_parent: QModelIndex) -> bool:
        """
        重写筛选方法，决定是否接受某一行
        仅当筛选文本不为空时才进行筛选
        """
        if not self._filter_text:
            return True

        source_model = self.sourceModel()
        if not isinstance(source_model, SqlitePaginationModel):
            return True

        # 检查当前行的所有筛选字段
        for col, field_name in enumerate(source_model.data_fields):
            # 只检查指定的筛选字段
            if self._filter_fields and field_name not in self._filter_fields:
                continue

            index = source_model.index(source_row, col, source_parent)
            data = source_model.data(index, Qt.ItemDataRole.DisplayRole)
            if data and self._filter_text in str(data).lower():
                return True

        return False

    def sort(self, column: int, order: Qt.SortOrder):
        self.sourceModel().sort(column, order)
        self.on_sort.emit()
        self.invalidateFilter()

    def currentPage(self) -> int:
        return self.sourceModel().current_page

    def totalPages(self) -> int:
        return self.sourceModel().total_pages()

    def itemsPerPage(self) -> int:
        return self.sourceModel().items_per_page

    def loadPage(self, page: int) -> bool:
        return self.sourceModel().load_page(page)

    def setItemsPerPage(self, count: int) -> None:
        self.sourceModel().set_items_per_page(count)

    def getItem(self, index: QModelIndex) -> Optional[IdenticalModel]:
        """获取指定索引的项目"""
        return self.sourceModel().get_item(index)

    def get_column_index(self, field_name: str) -> int:
        """获取字段名对应的列索引"""
        try:
            return self.sourceModel().display_fields.index(field_name)
        except ValueError:
            return -1

    def delete_batch(self, indices: List[QModelIndex]) -> bool:
        """删除选中的项目"""
        source_model = self.sourceModel()
        return source_model.delete_batch(indices)

    def handle_add_one_row(self) -> None:
        """处理添加一行的操作"""
        self.sourceModel().handle_add_row()

    def update_or_insert_batch_models(self, records: Sequence[IdenticalModel]) -> bool:
        """更新或插入批量记录"""
        return self.sourceModel().update_or_insert_batch_models(records)

    def export_to_excel(self, file_path: str) -> bool:
        """导出数据到Excel文件"""
        return self.sourceModel().export_to_excel(file_path)

    def add_one_model(self, record: IdenticalModel) -> None:
        """添加一条记录"""
        self.sourceModel().add_one_model(record)

    def get_all_models(self) -> List[IdenticalModel]:
        """获取所有记录"""
        return self.sourceModel().get_all_data()

    def delete_batch_by_models(self, models: List[IdenticalModel]) -> bool:
        """批量删除指定模型的记录"""
        return self.sourceModel().delete_batch_by_models(models)
